

<!DOCTYPE html>
<html lang="zh-CN" data-default-color-scheme=auto>



<head>
  <meta charset="UTF-8">
  <link rel="apple-touch-icon" sizes="76x76" href="/img/fluid.png">
  <link rel="icon" href="/img/fluid.png">
  <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=5.0, shrink-to-fit=no">
  <meta http-equiv="x-ua-compatible" content="ie=edge">
  
  <meta name="theme-color" content="#2f4154">
  <meta name="author" content="John Doe">
  <meta name="keywords" content="">
  
    <meta name="description" content="@[toc] 参考资料 SPOC 一、MyBatis SQL映射文件——输入输出 学习目标：  掌握通过SQL映射文件进行增删改查 掌握参数的使用 掌握resultMap  1.1 SQL映射的XML文件 *Mapper.xmlSQL 映射 XML文件 的顶级标签（有前后顺序）    标签名 属性 &#x2F; 描述    mapper 最外层标签，属性 namespace，用于指定Mapper接">
<meta property="og:type" content="article">
<meta property="og:title" content="MyBatis 笔记2 SQL映射XML文件标签 select、insert、update _ XML获取多个参数 _ MyBatis实现多表查询 _ resultMap标签映射">
<meta property="og:url" content="http://example.com/2022/01/14/MyBatis%20%E7%AC%94%E8%AE%B02%20SQL%E6%98%A0%E5%B0%84XML%E6%96%87%E4%BB%B6%E6%A0%87%E7%AD%BE%20select%E3%80%81insert%E3%80%81update%20_%20XML%E8%8E%B7%E5%8F%96%E5%A4%9A%E4%B8%AA%E5%8F%82%E6%95%B0%20_%20MyBatis%E5%AE%9E%E7%8E%B0%E5%A4%9A%E8%A1%A8%E6%9F%A5%E8%AF%A2%20_%20resultMap%E6%A0%87%E7%AD%BE%E6%98%A0%E5%B0%84/index.html">
<meta property="og:site_name" content="Hexo">
<meta property="og:description" content="@[toc] 参考资料 SPOC 一、MyBatis SQL映射文件——输入输出 学习目标：  掌握通过SQL映射文件进行增删改查 掌握参数的使用 掌握resultMap  1.1 SQL映射的XML文件 *Mapper.xmlSQL 映射 XML文件 的顶级标签（有前后顺序）    标签名 属性 &#x2F; 描述    mapper 最外层标签，属性 namespace，用于指定Mapper接">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://img-blog.csdnimg.cn/b38c7ac1b79f4b6abef1951cb7b8d256.png">
<meta property="og:image" content="https://img-blog.csdnimg.cn/c887e85e320e428ab44bf54599f506fd.png">
<meta property="article:published_time" content="2022-01-14T06:20:05.000Z">
<meta property="article:modified_time" content="2022-08-22T15:46:47.178Z">
<meta property="article:author" content="John Doe">
<meta property="article:tag" content="MyBatis">
<meta name="twitter:card" content="summary_large_image">
<meta name="twitter:image" content="https://img-blog.csdnimg.cn/b38c7ac1b79f4b6abef1951cb7b8d256.png">
  
  
  
  <title>MyBatis 笔记2 SQL映射XML文件标签 select、insert、update _ XML获取多个参数 _ MyBatis实现多表查询 _ resultMap标签映射 - Hexo</title>

  <link  rel="stylesheet" href="https://lib.baomitu.com/twitter-bootstrap/4.6.1/css/bootstrap.min.css" />



  <link  rel="stylesheet" href="https://lib.baomitu.com/github-markdown-css/4.0.0/github-markdown.min.css" />

  <link  rel="stylesheet" href="https://lib.baomitu.com/hint.css/2.7.0/hint.min.css" />

  <link  rel="stylesheet" href="https://lib.baomitu.com/fancybox/3.5.7/jquery.fancybox.min.css" />



<!-- 主题依赖的图标库，不要自行修改 -->
<!-- Do not modify the link that theme dependent icons -->

<link rel="stylesheet" href="//at.alicdn.com/t/font_1749284_hj8rtnfg7um.css">



<link rel="stylesheet" href="//at.alicdn.com/t/font_1736178_lbnruvf0jn.css">


<link  rel="stylesheet" href="/css/main.css" />


  <link id="highlight-css" rel="stylesheet" href="/css/highlight.css" />
  
    <link id="highlight-css-dark" rel="stylesheet" href="/css/highlight-dark.css" />
  




  <script id="fluid-configs">
    var Fluid = window.Fluid || {};
    Fluid.ctx = Object.assign({}, Fluid.ctx)
    var CONFIG = {"hostname":"example.com","root":"/","version":"1.9.2","typing":{"enable":true,"typeSpeed":70,"cursorChar":"_","loop":false,"scope":[]},"anchorjs":{"enable":true,"element":"h1,h2,h3,h4,h5,h6","placement":"left","visible":"hover","icon":""},"progressbar":{"enable":true,"height_px":3,"color":"#29d","options":{"showSpinner":false,"trickleSpeed":100}},"code_language":{"enable":true,"default":"TEXT"},"copy_btn":true,"image_caption":{"enable":true},"image_zoom":{"enable":true,"img_url_replace":["",""]},"toc":{"enable":true,"placement":"right","headingSelector":"h1,h2,h3,h4,h5,h6","collapseDepth":0},"lazyload":{"enable":true,"loading_img":"/img/loading.gif","onlypost":false,"offset_factor":2},"web_analytics":{"enable":false,"follow_dnt":true,"baidu":null,"google":null,"gtag":null,"tencent":{"sid":null,"cid":null},"woyaola":null,"cnzz":null,"leancloud":{"app_id":null,"app_key":null,"server_url":null,"path":"window.location.pathname","ignore_local":false}},"search_path":"/local-search.xml"};

    if (CONFIG.web_analytics.follow_dnt) {
      var dntVal = navigator.doNotTrack || window.doNotTrack || navigator.msDoNotTrack;
      Fluid.ctx.dnt = dntVal && (dntVal.startsWith('1') || dntVal.startsWith('yes') || dntVal.startsWith('on'));
    }
  </script>
  <script  src="/js/utils.js" ></script>
  <script  src="/js/color-schema.js" ></script>
  


  
<meta name="generator" content="Hexo 6.2.0"></head>


<body>
  

  <header>
    

<div class="header-inner" style="height: 70vh;">
  <nav id="navbar" class="navbar fixed-top  navbar-expand-lg navbar-dark scrolling-navbar">
  <div class="container">
    <a class="navbar-brand" href="/">
      <strong>尤 Ni&#39;s Blog</strong>
    </a>

    <button id="navbar-toggler-btn" class="navbar-toggler" type="button" data-toggle="collapse"
            data-target="#navbarSupportedContent"
            aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation">
      <div class="animated-icon"><span></span><span></span><span></span></div>
    </button>

    <!-- Collapsible content -->
    <div class="collapse navbar-collapse" id="navbarSupportedContent">
      <ul class="navbar-nav ml-auto text-center">
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/">
                <i class="iconfont icon-home-fill"></i>
                首页
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/archives/">
                <i class="iconfont icon-archive-fill"></i>
                归档
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/categories/">
                <i class="iconfont icon-category-fill"></i>
                分类
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/tags/">
                <i class="iconfont icon-tags-fill"></i>
                标签
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/about/">
                <i class="iconfont icon-user-fill"></i>
                关于
              </a>
            </li>
          
        
        
          <li class="nav-item" id="search-btn">
            <a class="nav-link" target="_self" href="javascript:;" data-toggle="modal" data-target="#modalSearch" aria-label="Search">
              &nbsp;<i class="iconfont icon-search"></i>&nbsp;
            </a>
          </li>
          
        
        
          <li class="nav-item" id="color-toggle-btn">
            <a class="nav-link" target="_self" href="javascript:;" aria-label="Color Toggle">&nbsp;<i
                class="iconfont icon-dark" id="color-toggle-icon"></i>&nbsp;</a>
          </li>
        
      </ul>
    </div>
  </div>
</nav>

  

<div id="banner" class="banner" parallax=true
     style="background: url('/img/default.png') no-repeat center center; background-size: cover;">
  <div class="full-bg-img">
    <div class="mask flex-center" style="background-color: rgba(0, 0, 0, 0.3)">
      <div class="banner-text text-center fade-in-up">
        <div class="h2">
          
            <span id="subtitle" data-typed-text="MyBatis 笔记2 SQL映射XML文件标签 select、insert、update _ XML获取多个参数 _ MyBatis实现多表查询 _ resultMap标签映射"></span>
          
        </div>

        
          
  <div class="mt-3">
    
      <span class="post-meta mr-2">
        <i class="iconfont icon-author" aria-hidden="true"></i>
        John Doe
      </span>
    
    
      <span class="post-meta">
        <i class="iconfont icon-date-fill" aria-hidden="true"></i>
        <time datetime="2022-01-14 14:20" pubdate>
          2022年1月14日 下午
        </time>
      </span>
    
  </div>

  <div class="mt-1">
    
      <span class="post-meta mr-2">
        <i class="iconfont icon-chart"></i>
        
          11k 字
        
      </span>
    

    
      <span class="post-meta mr-2">
        <i class="iconfont icon-clock-fill"></i>
        
        
        
          89 分钟
        
      </span>
    

    
    
  </div>


        
      </div>

      
    </div>
  </div>
</div>

</div>

  </header>

  <main>
    
      

<div class="container-fluid nopadding-x">
  <div class="row nomargin-x">
    <div class="side-col d-none d-lg-block col-lg-2">
      

    </div>

    <div class="col-lg-8 nopadding-x-md">
      <div class="container nopadding-x-md" id="board-ctn">
        <div id="board">
          <article class="post-content mx-auto">
            <!-- SEO header -->
            <h1 style="display: none">MyBatis 笔记2 SQL映射XML文件标签 select、insert、update _ XML获取多个参数 _ MyBatis实现多表查询 _ resultMap标签映射</h1>
            
              <p class="note note-info">
                
                  
                    本文最后更新于：1 小时前
                  
                
              </p>
            
            
              <div class="markdown-body">
                
                <p>@[toc]</p>
<h1 id="参考资料"><a href="#参考资料" class="headerlink" title="参考资料"></a>参考资料</h1><hr>
<p><a target="_blank" rel="noopener" href="http://spoc.wzu.edu.cn/study/initplay/780389.mooc">SPOC</a></p>
<h1 id="一、MyBatis-SQL映射文件——输入输出"><a href="#一、MyBatis-SQL映射文件——输入输出" class="headerlink" title="一、MyBatis SQL映射文件——输入输出"></a>一、MyBatis SQL映射文件——输入输出</h1><hr>
<p>学习目标：</p>
<ul>
<li>掌握通过SQL映射文件进行增删改查</li>
<li>掌握参数的使用</li>
<li>掌握resultMap</li>
</ul>
<h2 id="1-1-SQL映射的XML文件-Mapper-xml"><a href="#1-1-SQL映射的XML文件-Mapper-xml" class="headerlink" title="1.1 SQL映射的XML文件 *Mapper.xml"></a>1.1 SQL映射的XML文件 *Mapper.xml</h2><p>SQL 映射 XML文件 的顶级标签（有前后顺序）</p>
<table>
<thead>
<tr>
<th>标签名</th>
<th>属性 &#x2F; 描述</th>
</tr>
</thead>
<tbody><tr>
<td>mapper</td>
<td>最外层标签，属性 namespace，用于指定Mapper接口类的引用位置，比如com.uni.UserMapper</td>
</tr>
<tr>
<td>cache</td>
<td>配置给定命名空间的缓存</td>
</tr>
<tr>
<td>cache-ref</td>
<td>从其他命名空间引用缓存配置</td>
</tr>
<tr>
<td>resultMap</td>
<td>用来描述数据库结果集和对象的对应关系</td>
</tr>
<tr>
<td>sql</td>
<td>可以重用的SQL块，也可以被其他语句引用</td>
</tr>
<tr>
<td>insert</td>
<td>映射插入语句</td>
</tr>
<tr>
<td>update</td>
<td>映射更新语句</td>
</tr>
<tr>
<td>delete</td>
<td>映射删除语句</td>
</tr>
<tr>
<td>select</td>
<td>映射查询语句</td>
</tr>
</tbody></table>
<h2 id="1-2-mapper-标签"><a href="#1-2-mapper-标签" class="headerlink" title="1.2 mapper 标签"></a>1.2 mapper 标签</h2><p>属性：namespace 表示命名空间</p>
<ul>
<li>namespace和子标签（select、insert、update等）的id <strong>联合保证唯一</strong>，区别不同的mapper</li>
<li>绑定DAO接口<ul>
<li>namespace的命名必须跟某个接口同名</li>
<li>接口中的方法与映射文件中SQL语句id一一对应</li>
</ul>
</li>
</ul>
<figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><code class="hljs xml"><span class="hljs-meta">&lt;?xml version=<span class="hljs-string">&quot;1.0&quot;</span> encoding=<span class="hljs-string">&quot;UTF-8&quot;</span> ?&gt;</span><br><span class="hljs-meta">&lt;!DOCTYPE <span class="hljs-keyword">mapper</span></span><br><span class="hljs-meta"><span class="hljs-keyword">PUBLIC</span> <span class="hljs-string">&quot;-//mybatis.org//DTD Mapper 3.0//EN&quot;</span></span><br><span class="hljs-meta"><span class="hljs-string">&quot;http://mybatis.org/dtd/mybatis-3-mapper.dtd&quot;</span>&gt;</span><br><br><span class="hljs-tag">&lt;<span class="hljs-name">mapper</span> <span class="hljs-attr">namespace</span>=<span class="hljs-string">&quot;com.uni.User&quot;</span>&gt;</span> <br>	<span class="hljs-tag">&lt;<span class="hljs-name">select</span> <span class="hljs-attr">id</span>= <span class="hljs-string">&quot;queryAllUser&quot;</span> <span class="hljs-attr">...</span>&gt;</span><br>    	...<br>    <span class="hljs-tag">&lt;/<span class="hljs-name">select</span>&gt;</span><br><br><span class="hljs-tag">&lt;/<span class="hljs-name">mapper</span>&gt;</span><br></code></pre></td></tr></table></figure>



<h2 id="1-3-select-标签"><a href="#1-3-select-标签" class="headerlink" title="1.3 select 标签"></a>1.3 select 标签</h2><ul>
<li>select 是MyBatis中最常用的元素之一</li>
<li>select 语句有很多属性可以详细配置每一条语句</li>
</ul>
<table>
<thead>
<tr>
<th>属性</th>
<th>描述</th>
</tr>
</thead>
<tbody><tr>
<td>id</td>
<td>命名空间中唯一的标识符，接口中的<strong>方法</strong>与映射文件中SQL语句一 一对应</td>
</tr>
<tr>
<td>parameterType</td>
<td>传入SQL语句的参数类型</td>
</tr>
<tr>
<td>resultType</td>
<td>SQL语句返回值类型的完整类名或者别名</td>
</tr>
</tbody></table>
<h2 id="1-4-XML中获取传入的参数"><a href="#1-4-XML中获取传入的参数" class="headerlink" title="1.4 XML中获取传入的参数"></a>1.4 XML中获取传入的参数</h2><p>查询单个用户通常是需要指定id的，比如在mysql中是这样的：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> <span class="hljs-keyword">user</span> <span class="hljs-keyword">where</span> id <span class="hljs-operator">=</span> <span class="hljs-number">1</span>;<br></code></pre></td></tr></table></figure>

<p>所以在接口中可以这样定义方法：</p>
<p><code>UserMapper.java</code></p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><code class="hljs java"><span class="hljs-keyword">public</span> <span class="hljs-keyword">interface</span> <span class="hljs-title class_">UserMapper</span>&#123;<br>    User <span class="hljs-title function_">queryUserById</span><span class="hljs-params">(<span class="hljs-type">int</span> id)</span>;<br>&#125;<br></code></pre></td></tr></table></figure>

<p>而在XML中如何获取到这个传入的参数呢？</p>
<p>这里可以通过<code>#&#123;&#125;</code> 获取这个参数，若只有一个参数，<code>parameterType</code> 参数可以省略不写，#{}里的内容可任意填，不过只能取一个）</p>
<p><code>#&#123;&#125;</code>不能用于字符串拼接</p>
<p><code>UserMapper.xml</code></p>
<figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><code class="hljs xml"><span class="hljs-comment">&lt;!-- 根据ID查询用户 --&gt;</span><br><span class="hljs-tag">&lt;<span class="hljs-name">select</span> <span class="hljs-attr">id</span>=<span class="hljs-string">&quot;queryUserById&quot;</span> <span class="hljs-attr">resultType</span>=<span class="hljs-string">&quot;com.uni.pojo.User&quot;</span>&gt;</span><br>    select * from smbms_user where id = #&#123;id&#125;<br><span class="hljs-tag">&lt;/<span class="hljs-name">select</span>&gt;</span><br></code></pre></td></tr></table></figure>

<p>测试：</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><code class="hljs java"><span class="hljs-meta">@Test</span><br><span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">testQueryUserById</span><span class="hljs-params">()</span>&#123;<br>    <span class="hljs-type">SqlSession</span> <span class="hljs-variable">sqlSession</span> <span class="hljs-operator">=</span> MyBatisUtil.getSession();<br>    <span class="hljs-type">User</span> <span class="hljs-variable">user</span> <span class="hljs-operator">=</span> sqlSession.getMapper(UserMapper.class).queryUserById(<span class="hljs-number">1</span>);<br>    System.out.println(user);<br>    sqlSession.close();<br>&#125;<br></code></pre></td></tr></table></figure>

<h3 id="1-4-1-多参案例（一）模糊查询"><a href="#1-4-1-多参案例（一）模糊查询" class="headerlink" title="1.4.1 多参案例（一）模糊查询"></a>1.4.1 多参案例（一）模糊查询</h3><p>现使用 <code>smbms_user</code>表的username用户名和sex性别两个字段来进行模糊查询，使用到的sql语句：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">select</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> smbms_user <span class="hljs-keyword">where</span> userName <span class="hljs-keyword">like</span> CONCAT(<span class="hljs-string">&#x27;%超%&#x27;</span>) <span class="hljs-keyword">and</span> sex <span class="hljs-operator">=</span> <span class="hljs-string">&#x27;男&#x27;</span>;<br></code></pre></td></tr></table></figure>

<p>这句话能查询带超字且性别为男的所有用户，现使用MyBatis实现，要点有：</p>
<ul>
<li>parameterType可不写</li>
<li>使用#{param1} 表示第一个参数</li>
</ul>
<p>原理：</p>
<ul>
<li>自动封装成Map类型的数据，key: param1 .. paramN，或者参数的索引， value: 传入的参数值</li>
<li>自定义名称 @Param注解</li>
</ul>
<p><strong>方式一：使用param按顺序取参</strong></p>
<p><code>UserMapper.java</code></p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs java">List&lt;User&gt; <span class="hljs-title function_">queryUserLikeNameAndGender</span><span class="hljs-params">(String name, <span class="hljs-type">int</span> gender)</span>;<br></code></pre></td></tr></table></figure>

<p><code>UserMapper.xml</code></p>
<figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><code class="hljs xml"><span class="hljs-comment">&lt;!-- 根据名称和性别进行模糊查询 --&gt;</span><br><span class="hljs-tag">&lt;<span class="hljs-name">select</span> <span class="hljs-attr">id</span>=<span class="hljs-string">&quot;queryUserLikeNameAndGender&quot;</span> <span class="hljs-attr">resultType</span>=<span class="hljs-string">&quot;com.uni.pojo.User&quot;</span>&gt;</span><br>    select * from smbms_user where userName like CONCAT(&#x27;%&#x27;, #&#123;param1&#125;, &#x27;%&#x27;) AND gender = #&#123;param2&#125;<br><span class="hljs-tag">&lt;/<span class="hljs-name">select</span>&gt;</span><br></code></pre></td></tr></table></figure>

<p><strong>方式二：使用@Param注解取参</strong></p>
<p><code>UserMapper.java</code></p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs java">List&lt;User&gt; <span class="hljs-title function_">queryUserLikeNameAndGender</span><span class="hljs-params">(<span class="hljs-meta">@Param(&quot;userName&quot;)</span> String name, <span class="hljs-meta">@Param(&quot;gender&quot;)</span> <span class="hljs-type">int</span> gender)</span>;<br></code></pre></td></tr></table></figure>

<p><code>UserMapper.xml</code></p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><code class="hljs java">&lt;select id=<span class="hljs-string">&quot;queryUserLikeNameAndGender&quot;</span> resultType=<span class="hljs-string">&quot;com.uni.pojo.User&quot;</span>&gt;<br>	select * from smbms_user where userName like <span class="hljs-title function_">CONCAT</span><span class="hljs-params">(<span class="hljs-string">&#x27;%&#x27;</span>, #&#123;userName&#125;, <span class="hljs-string">&#x27;%&#x27;</span>)</span> <span class="hljs-type">AND</span> <span class="hljs-variable">gender</span> <span class="hljs-operator">=</span> #&#123;gender&#125;<br>&lt;/select&gt;<br></code></pre></td></tr></table></figure>

<h3 id="1-4-2-多参实例（二）parameterType模糊查询"><a href="#1-4-2-多参实例（二）parameterType模糊查询" class="headerlink" title="1.4.2 多参实例（二）parameterType模糊查询"></a>1.4.2 多参实例（二）parameterType模糊查询</h3><p>paramterType参数的作用：指定SQL语句传入的参数类型</p>
<p>在XML中，配置了paramterType为实体类后可以使用#{实体类.成员变量}的方式来进行取值</p>
<p>案例：根据用户的名称和性别进行模糊查询</p>
<p><code>User.java</code></p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><code class="hljs java"><span class="hljs-keyword">public</span> <span class="hljs-keyword">class</span> <span class="hljs-title class_">User</span> &#123;<br>	<span class="hljs-keyword">private</span> Integer id; <span class="hljs-comment">//id</span><br>	<span class="hljs-keyword">private</span> String userName; <span class="hljs-comment">//用户名称</span><br>    <span class="hljs-keyword">private</span> Integer gender;  <span class="hljs-comment">//性别    </span><br>    ...<br>    <span class="hljs-comment">//get、set、toString() 略</span><br>&#125;<br></code></pre></td></tr></table></figure>

<p><code>UserMapper.java</code></p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs java">List&lt;User&gt; <span class="hljs-title function_">queryUserLikeNameAndGender</span><span class="hljs-params">(User user)</span>;<br></code></pre></td></tr></table></figure>

<p><code>UserMapper.xml</code></p>
<figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><code class="hljs xml"><span class="hljs-tag">&lt;<span class="hljs-name">select</span> <span class="hljs-attr">id</span>=<span class="hljs-string">&quot;queryUserLikeNameAndGender&quot;</span> <span class="hljs-attr">parameterType</span>=<span class="hljs-string">&quot;com.uni.pojo.User&quot;</span> <span class="hljs-attr">resultType</span>=<span class="hljs-string">&quot;com.uni.pojo.User&quot;</span>&gt;</span><br>    select * from smbms_user where userName like CONCAT(&#x27;%&#x27;, #&#123;userName&#125;, &#x27;%&#x27;) AND gender = #&#123;gender&#125;<br><span class="hljs-tag">&lt;/<span class="hljs-name">select</span>&gt;</span><br></code></pre></td></tr></table></figure>

<p><code>Test.java</code></p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><code class="hljs java"><span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">testQueryUserLikeNameAndGender</span><span class="hljs-params">()</span>&#123;<br>    <span class="hljs-type">SqlSession</span> <span class="hljs-variable">sqlSession</span> <span class="hljs-operator">=</span> MyBatisUtil.getSession();<br>    <span class="hljs-type">User</span> <span class="hljs-variable">user</span> <span class="hljs-operator">=</span> <span class="hljs-keyword">new</span> <span class="hljs-title class_">User</span>();<br>    user.setUserName(<span class="hljs-string">&quot;孙&quot;</span>);<br>    user.setGender(<span class="hljs-number">2</span>);<br>    List&lt;User&gt; users = sqlSession.getMapper(UserMapper.class).queryUserLikeNameAndGender(user);<br>    users.forEach(u -&gt; System.out.println(u));<br>    sqlSession.close();<br>&#125;<br></code></pre></td></tr></table></figure>

<h2 id="1-5-insert-标签"><a href="#1-5-insert-标签" class="headerlink" title="1.5 insert 标签"></a>1.5 insert 标签</h2><p>insert标签是实现sql的insert语句，所以必须指定paramterType属性，但可以没有resultType属性。</p>
<p>比如：</p>
<p><code>UserMapper.java</code></p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs java"><span class="hljs-keyword">void</span> <span class="hljs-title function_">insertUser</span><span class="hljs-params">(User user)</span>;<br></code></pre></td></tr></table></figure>

<p><code>UserMapper.xml</code></p>
<figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><code class="hljs xml"><span class="hljs-tag">&lt;<span class="hljs-name">insert</span> <span class="hljs-attr">id</span>=<span class="hljs-string">&quot;insertUser&quot;</span> <span class="hljs-attr">parameter</span>=<span class="hljs-string">&quot;com.uni.pojo.User&quot;</span>&gt;</span><br>	insert into smbms_user(userCode, userName, userPassword) values(#&#123;userCode&#125;, #&#123;userName&#125;, #&#123;userPassword&#125;)<br><span class="hljs-tag">&lt;/<span class="hljs-name">insert</span>&gt;</span><br></code></pre></td></tr></table></figure>

<p>注：<code>insert</code>、<code>update</code>、<code>delete</code>标签均可以不指定 <code>resultType</code>属性</p>
<h2 id="1-6-update-标签"><a href="#1-6-update-标签" class="headerlink" title="1.6 update 标签"></a>1.6 update 标签</h2><p>主要属性：id 、parameterType</p>
<p>案例：修改密码</p>
<p><code>UserMapper.xml</code></p>
<figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><code class="hljs xml"><span class="hljs-tag">&lt;<span class="hljs-name">update</span> <span class="hljs-attr">id</span>=<span class="hljs-string">&quot;modify&quot;</span> <span class="hljs-attr">parameter</span>=<span class="hljs-string">&quot;com.uni.pojo.User&quot;</span>&gt;</span><br>	update smbms_user set userCode = #&#123;userCode&#125;, userName = #&#123;userName&#125;, userPassword=#&#123;userPassword&#125; where id = #&#123;id&#125;<br><span class="hljs-tag">&lt;/<span class="hljs-name">update</span>&gt;</span><br></code></pre></td></tr></table></figure>



<h2 id="1-7-delete-标签"><a href="#1-7-delete-标签" class="headerlink" title="1.7 delete 标签"></a>1.7 delete 标签</h2><p>主要属性：id、parameterType</p>
<p>案例：删除指定id的用户</p>
<p><code>UserMapper.xml</code></p>
<figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><code class="hljs xml"><span class="hljs-tag">&lt;<span class="hljs-name">delete</span> <span class="hljs-attr">id</span> = <span class="hljs-string">&quot;deleteUserById&quot;</span> <span class="hljs-attr">parameterType</span>=<span class="hljs-string">&quot;int&quot;</span>&gt;</span><br>	delete from smbms_user where id = #&#123;id&#125;<br><span class="hljs-tag">&lt;/<span class="hljs-name">delete</span>&gt;</span><br></code></pre></td></tr></table></figure>
<h2 id="1-8-小结"><a href="#1-8-小结" class="headerlink" title="1.8 小结"></a>1.8 小结</h2><ul>
<li><p>parameterType</p>
<ul>
<li><p>基础数据类型</p>
<ul>
<li>int、String、Date等</li>
<li>只能传入一个，通过#{参数名} 可获取传入的值</li>
</ul>
</li>
<li><p>复杂数据类型</p>
<ul>
<li>java实体类、Map等</li>
<li>通过#{属性名} 或者 #{map的keyName}即可获取传入值</li>
<li>${} 一般很少用 （不支持占位符的时候使用）</li>
</ul>
</li>
</ul>
</li>
<li><p>resultType</p>
<ul>
<li>返回基本类型、POJO类型</li>
<li>返回List集合，resultType还是POJO类型</li>
<li>返回Map集合，resultType还是POJO类型（不常用）</li>
</ul>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><code class="hljs java"><span class="hljs-meta">@MapKey(&quot;id&quot;)</span><br><span class="hljs-keyword">public</span> Map&lt;Integer, User&gt; <span class="hljs-title function_">findUserByNameReturnMap</span><span class="hljs-params">(<span class="hljs-meta">@Param(&quot;lastName&quot;)</span>String lastName)</span>;<br></code></pre></td></tr></table></figure>

<ul>
<li>指定返回的Map集合以id为key，也可指定对象别的属性为key</li>
</ul>
</li>
</ul>
<p><code>UserMapper.xml</code></p>
<figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><code class="hljs xml"><span class="hljs-tag">&lt;<span class="hljs-name">select</span> <span class="hljs-attr">id</span>=<span class="hljs-string">&quot;findUserByNameReturnMap&quot;</span> <span class="hljs-attr">resultType</span>=<span class="hljs-string">&quot;myUser&quot;</span>&gt;</span><br>	select * from user where username like &#x27;$&#123;lastName&#125;&#x27;<br><span class="hljs-tag">&lt;/<span class="hljs-name">select</span>&gt;</span><br></code></pre></td></tr></table></figure>

<h1 id="二、MyBatis-SQL-映射文件-——复杂查询"><a href="#二、MyBatis-SQL-映射文件-——复杂查询" class="headerlink" title="二、MyBatis SQL 映射文件 ——复杂查询"></a>二、MyBatis SQL 映射文件 ——复杂查询</h1><h2 id="2-1-SQL查询-关联表（外键关系）"><a href="#2-1-SQL查询-关联表（外键关系）" class="headerlink" title="2.1 SQL查询 - 关联表（外键关系）"></a>2.1 SQL查询 - 关联表（外键关系）</h2><p>现有 <code>smbms_user</code> 用户表和<code>smbms_role</code>用户角色表</p>
<p>用户表的userRole字段指定为用户角色表的ID</p>
<p><img src="https://img-blog.csdnimg.cn/b38c7ac1b79f4b6abef1951cb7b8d256.png" srcset="/img/loading.gif" lazyload alt="在这里插入图片描述"></p>
<p>现若要显示用户信息包括角色的名称，需要用到多表查询，同样以模糊查询为例，查找姓孙而且是普通员工的SQL语句如下：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> u.userName,r.roleName <span class="hljs-keyword">FROM</span> smbms_user <span class="hljs-keyword">AS</span> u, smbms_role <span class="hljs-keyword">AS</span> r<br><span class="hljs-keyword">WHERE</span> u.userName <span class="hljs-keyword">LIKE</span> CONCAT (<span class="hljs-string">&#x27;%&#x27;</span>, <span class="hljs-string">&#x27;孙&#x27;</span>, <span class="hljs-string">&#x27;%&#x27;</span>)<br><span class="hljs-keyword">AND</span> u.userRole <span class="hljs-operator">=</span> <span class="hljs-number">3</span> <span class="hljs-keyword">AND</span> u.userRole <span class="hljs-operator">=</span> r.id<br></code></pre></td></tr></table></figure>

<p>此时的结果是：</p>
<p><img src="https://img-blog.csdnimg.cn/c887e85e320e428ab44bf54599f506fd.png" srcset="/img/loading.gif" lazyload alt="在这里插入图片描述"></p>
<p>除了User用户本身的信息以外，还多了一个roleName，即角色的名称，所以要想使用MyBatis框架实现多表关联，则需要将Role实体类定义在User实体类中，表示当前用户的角色信息。</p>
<h2 id="2-2-对象关联"><a href="#2-2-对象关联" class="headerlink" title="2.2 对象关联"></a>2.2 对象关联</h2><p>对象关联的方式有两种</p>
<ul>
<li>POJO之间引入聚合关系<ul>
<li>一对多（1角色，多用户）</li>
<li>一对一（1订单，1用户）</li>
<li>多对一（多用户，1角色）</li>
<li>多对多（订单，产品）</li>
</ul>
</li>
<li>通常在系统设计的时候设定好类之间的关系</li>
</ul>
<h2 id="2-3-resultType-与-resultMap"><a href="#2-3-resultType-与-resultMap" class="headerlink" title="2.3 resultType 与 resultMap"></a>2.3 resultType 与 resultMap</h2><ul>
<li>resultType：直接表示返回类型<ul>
<li>基本数据类型</li>
<li>复杂数据类型（没有关联查询时）</li>
</ul>
</li>
<li>resultMap：对外部resultMap的引用<ul>
<li>应用场景：<ul>
<li>数据库字段信息与对象属性不一致</li>
<li>复杂的联合查询，自由控制映射结果</li>
</ul>
</li>
</ul>
</li>
</ul>
<p>注：resultType 与 resultMap 不能同时存在</p>
<h2 id="2-4-一对多查询案例"><a href="#2-4-一对多查询案例" class="headerlink" title="2.4 一对多查询案例"></a>2.4 一对多查询案例</h2><p>以之前的两张表为例，一个角色可对应多个用户。</p>
<p><code>Role.java</code></p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br></pre></td><td class="code"><pre><code class="hljs java"><span class="hljs-keyword">package</span> com.uni.pojo;<br><br><span class="hljs-keyword">import</span> lombok.AllArgsConstructor;<br><span class="hljs-keyword">import</span> lombok.Data;<br><span class="hljs-keyword">import</span> lombok.NoArgsConstructor;<br><span class="hljs-keyword">import</span> java.sql.Date;<br><br><span class="hljs-meta">@Data</span><br><span class="hljs-meta">@AllArgsConstructor</span><br><span class="hljs-meta">@NoArgsConstructor</span><br><span class="hljs-keyword">public</span> <span class="hljs-keyword">class</span> <span class="hljs-title class_">Role</span> &#123;<br>    <span class="hljs-keyword">private</span> <span class="hljs-type">int</span> id;<br>    <span class="hljs-keyword">private</span> String roleCode;<br>    <span class="hljs-keyword">private</span> String roleName;<br>    <span class="hljs-keyword">private</span> <span class="hljs-type">int</span> createdBy;<br>    <span class="hljs-keyword">private</span> Date creationDate;<br>    <span class="hljs-keyword">private</span> <span class="hljs-type">int</span> modifyBy;<br>    <span class="hljs-keyword">private</span> Date modifyDate;<br>    <span class="hljs-keyword">private</span> List&lt;User&gt; users;<br>&#125;<br></code></pre></td></tr></table></figure>



<p><code>User.java</code></p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><code class="hljs java"><span class="hljs-keyword">package</span> com.uni.pojo;<br><br><span class="hljs-keyword">import</span> lombok.AllArgsConstructor;<br><span class="hljs-keyword">import</span> lombok.Data;<br><span class="hljs-keyword">import</span> lombok.NoArgsConstructor;<br><br><span class="hljs-keyword">import</span> java.util.Date;<br><span class="hljs-meta">@NoArgsConstructor</span><br><span class="hljs-meta">@AllArgsConstructor</span><br><span class="hljs-meta">@Data</span><br><span class="hljs-keyword">public</span> <span class="hljs-keyword">class</span> <span class="hljs-title class_">User</span> &#123;<br>	<span class="hljs-keyword">private</span> Integer id; <span class="hljs-comment">//id </span><br>	<span class="hljs-keyword">private</span> String userCode; <span class="hljs-comment">//用户编码</span><br>	<span class="hljs-keyword">private</span> String userName; <span class="hljs-comment">//用户名称</span><br>    <span class="hljs-keyword">private</span> Role role; 	<span class="hljs-comment">// 用户角色</span><br>    <span class="hljs-comment">// ...</span><br>&#125;<br></code></pre></td></tr></table></figure>

<p>案例：查询跟当前用户相同角色的所有用户</p>
<p><code>UserMapper.java</code></p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs java">Role <span class="hljs-title function_">queryAllUserSameRole</span><span class="hljs-params">(User user)</span>;     <span class="hljs-comment">// 查询跟当前用户相同角色的所有用户</span><br></code></pre></td></tr></table></figure>



<p><code>UserMapper.xml</code></p>
<figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><code class="hljs xml"><span class="hljs-comment">&lt;!-- 查询所有的用户名称和角色名称 --&gt;</span><br><span class="hljs-tag">&lt;<span class="hljs-name">select</span> <span class="hljs-attr">id</span>=<span class="hljs-string">&quot;queryAllUserSameRole&quot;</span> <span class="hljs-attr">parameterType</span>=<span class="hljs-string">&quot;com.uni.pojo.User&quot;</span> <span class="hljs-attr">resultMap</span>=<span class="hljs-string">&quot;oneRole&quot;</span>&gt;</span><br>    SELECT u.userName,r.roleName FROM smbms_user AS u, smbms_role AS r<br>    WHERE u.userRole = r.id AND r.id = #&#123;userRole&#125;<br><span class="hljs-tag">&lt;/<span class="hljs-name">select</span>&gt;</span><br><br><span class="hljs-tag">&lt;<span class="hljs-name">resultMap</span> <span class="hljs-attr">id</span>=<span class="hljs-string">&quot;oneRole&quot;</span> <span class="hljs-attr">type</span>=<span class="hljs-string">&quot;com.uni.pojo.Role&quot;</span>&gt;</span><br>    <span class="hljs-tag">&lt;<span class="hljs-name">result</span> <span class="hljs-attr">property</span>=<span class="hljs-string">&quot;roleName&quot;</span> <span class="hljs-attr">column</span>=<span class="hljs-string">&quot;roleName&quot;</span>&gt;</span><span class="hljs-tag">&lt;/<span class="hljs-name">result</span>&gt;</span><br>    <span class="hljs-tag">&lt;<span class="hljs-name">collection</span> <span class="hljs-attr">property</span>=<span class="hljs-string">&quot;users&quot;</span> <span class="hljs-attr">ofType</span>=<span class="hljs-string">&quot;com.uni.pojo.User&quot;</span>&gt;</span><br>        <span class="hljs-tag">&lt;<span class="hljs-name">result</span> <span class="hljs-attr">property</span>=<span class="hljs-string">&quot;userName&quot;</span> <span class="hljs-attr">column</span>=<span class="hljs-string">&quot;userName&quot;</span>&gt;</span><span class="hljs-tag">&lt;/<span class="hljs-name">result</span>&gt;</span><br>    <span class="hljs-tag">&lt;/<span class="hljs-name">collection</span>&gt;</span><br><span class="hljs-tag">&lt;/<span class="hljs-name">resultMap</span>&gt;</span><br></code></pre></td></tr></table></figure>

<p><code>TestUserMapper.java</code></p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><code class="hljs java"><span class="hljs-meta">@Test</span><br><span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">testQueryAllUserNameAndRoleName</span><span class="hljs-params">()</span>&#123;<br>    <span class="hljs-type">SqlSession</span> <span class="hljs-variable">sqlSession</span> <span class="hljs-operator">=</span> MyBatisUtil.getSession();<br>    <span class="hljs-type">User</span> <span class="hljs-variable">user</span> <span class="hljs-operator">=</span> <span class="hljs-keyword">new</span> <span class="hljs-title class_">User</span>();<br>    user.setUserRole(<span class="hljs-number">3</span>);<br>    <span class="hljs-type">Role</span> <span class="hljs-variable">role</span> <span class="hljs-operator">=</span> sqlSession.getMapper(UserMapper.class).queryAllUserSameRole(user);<br>    <span class="hljs-keyword">for</span> (User u : role.getUsers()) &#123;<br>        System.out.println(role.getRoleName() + <span class="hljs-string">&quot;: &quot;</span> + u.getUserName());<br>    &#125;<br>    sqlSession.close();<br>&#125;<br></code></pre></td></tr></table></figure>



<p>一对多是一个实体类对象里，比如Role角色，含有列表形式的变量用户，表示当前角色的所有用户，此时的结果应当使用<code>&lt;resultMap&gt;</code>来进行处理，将查询结果的字段作为<code>column</code>属性的值，与其对应的则是<code>property</code>属性，这个属性表示实体类里的变量名，比如在这个Demo里就是重新注入了<code>Role</code>角色实体类。</p>
<p>为什么需要用到&lt;<code>colection&gt;</code>标签呢？</p>
<p>因为返回的结果当中，角色名都是相同的，不同的则是用户的信息，所以要用这个标签去给成员变量<code>List&lt;User&gt; user</code> 进行注入</p>
<p>在这个标签里的每个<code>&lt;result&gt;</code>标签是指当前的用户。</p>
<h2 id="2-5-多对一映射"><a href="#2-5-多对一映射" class="headerlink" title="2.5 多对一映射"></a>2.5 多对一映射</h2><ul>
<li><p><code>resultMap</code> 返回的是一个引用</p>
</li>
<li><p>返回类型还是POJO实体类，上述Demo里是Role类</p>
</li>
<li><p>对象中的各个属性根据查询得到的表数据进行映射</p>
</li>
<li><p>id属性需要单独设置<code>&lt;id&gt;</code> 标签（提高性能），其余属性为 <code>&lt;result&gt;</code> 标签</p>
<ul>
<li>property 属性指定类中的属性名，column 属性指定查询得到的表中的列名</li>
<li>多对一时，要用 <code>&lt;association&gt;</code>标签单独映射</li>
</ul>
</li>
<li><p>可根据需求映射属性（不用每个属性都映射，比如上个Demo只映射了角色名称和用户名称）</p>
</li>
</ul>
<p>案例：根据供应商的ID查询相关信息以及旗下所有的订单列表</p>
<p><code>Provider.java</code></p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br></pre></td><td class="code"><pre><code class="hljs java"><span class="hljs-keyword">package</span> com.uni.pojo;<br><br><span class="hljs-keyword">import</span> lombok.AllArgsConstructor;<br><span class="hljs-keyword">import</span> lombok.Data;<br><span class="hljs-keyword">import</span> lombok.NoArgsConstructor;<br><br><span class="hljs-keyword">import</span> java.sql.Date;<br><span class="hljs-keyword">import</span> java.util.List;<br><br><span class="hljs-meta">@NoArgsConstructor</span><br><span class="hljs-meta">@AllArgsConstructor</span><br><span class="hljs-meta">@Data</span><br><span class="hljs-comment">/* 供应商 */</span><br><span class="hljs-keyword">public</span> <span class="hljs-keyword">class</span> <span class="hljs-title class_">Provider</span> &#123;<br>    <span class="hljs-keyword">private</span> Integer id;<br>    <span class="hljs-keyword">private</span> String proName; <span class="hljs-comment">// 名称</span><br>    <span class="hljs-comment">// ...</span><br>&#125;<br></code></pre></td></tr></table></figure>

<p><code>Bill.java</code></p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><code class="hljs java"><span class="hljs-keyword">package</span> com.uni.pojo;<br><br><span class="hljs-keyword">import</span> lombok.AllArgsConstructor;<br><span class="hljs-keyword">import</span> lombok.Data;<br><span class="hljs-keyword">import</span> lombok.NoArgsConstructor;<br><br><span class="hljs-keyword">import</span> java.sql.Date;<br><br><span class="hljs-meta">@Data</span><br><span class="hljs-meta">@NoArgsConstructor</span><br><span class="hljs-meta">@AllArgsConstructor</span><br><span class="hljs-keyword">public</span> <span class="hljs-keyword">class</span> <span class="hljs-title class_">Bill</span> &#123;<br>    <span class="hljs-keyword">private</span> <span class="hljs-type">int</span> id;<span class="hljs-comment">//主键ID</span><br>    <span class="hljs-keyword">private</span> String productName;<span class="hljs-comment">//商品名称</span><br>    <span class="hljs-keyword">private</span> Provider provider; <span class="hljs-comment">// 供应商</span><br>	<span class="hljs-comment">//...</span><br>&#125;<br></code></pre></td></tr></table></figure>

<p><code>ProviderMapper.java</code></p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><code class="hljs java">List&lt;Bill&gt; <span class="hljs-title function_">queryProviderById</span><span class="hljs-params">(<span class="hljs-type">int</span> id)</span>;     <span class="hljs-comment">// 查询某个供应商的所有订单</span><br></code></pre></td></tr></table></figure>

<p><code>ProviderMapper.xml</code></p>
<figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><code class="hljs xml"><span class="hljs-tag">&lt;<span class="hljs-name">select</span> <span class="hljs-attr">id</span>=<span class="hljs-string">&quot;queryProviderById&quot;</span> <span class="hljs-attr">resultMap</span>=<span class="hljs-string">&quot;oneProvider&quot;</span>&gt;</span><br>    SELECT p.proName, b.productName FROM smbms_provider p, smbms_bill b<br>    WHERE p.id = #&#123;id&#125;<br><span class="hljs-tag">&lt;/<span class="hljs-name">select</span>&gt;</span><br><br><span class="hljs-tag">&lt;<span class="hljs-name">resultMap</span> <span class="hljs-attr">id</span>=<span class="hljs-string">&quot;oneProvider&quot;</span> <span class="hljs-attr">type</span>=<span class="hljs-string">&quot;Bill&quot;</span>&gt;</span><br>    <span class="hljs-tag">&lt;<span class="hljs-name">result</span> <span class="hljs-attr">property</span>=<span class="hljs-string">&quot;productName&quot;</span> <span class="hljs-attr">column</span>=<span class="hljs-string">&quot;productName&quot;</span>&gt;</span><span class="hljs-tag">&lt;/<span class="hljs-name">result</span>&gt;</span><br>    <span class="hljs-tag">&lt;<span class="hljs-name">association</span> <span class="hljs-attr">property</span>=<span class="hljs-string">&quot;provider&quot;</span>&gt;</span><br>        <span class="hljs-tag">&lt;<span class="hljs-name">result</span> <span class="hljs-attr">property</span>=<span class="hljs-string">&quot;proName&quot;</span> <span class="hljs-attr">column</span>=<span class="hljs-string">&quot;proName&quot;</span>&gt;</span><span class="hljs-tag">&lt;/<span class="hljs-name">result</span>&gt;</span><br>    <span class="hljs-tag">&lt;/<span class="hljs-name">association</span>&gt;</span><br><span class="hljs-tag">&lt;/<span class="hljs-name">resultMap</span>&gt;</span><br></code></pre></td></tr></table></figure>

<p><code>TestProviderMapper.java</code></p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><code class="hljs java">    <span class="hljs-meta">@Test</span><br>    <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">queryProviderById</span><span class="hljs-params">()</span>&#123;<br>        <span class="hljs-type">SqlSession</span> <span class="hljs-variable">sqlSession</span> <span class="hljs-operator">=</span> MyBatisUtil.getSession();<br>        List&lt;Bill&gt; bills = sqlSession.getMapper(ProviderMapper.class).queryProviderById(<span class="hljs-number">1</span>);<br>        <span class="hljs-keyword">for</span> (Bill bill : bills)<br>            System.out.println(bill.getProvider().getProName() + <span class="hljs-string">&quot;: &quot;</span> + bill.getProductName());<br>        sqlSession.close();<br>    &#125;<br>&#125;<br></code></pre></td></tr></table></figure>



<h2 id="2-6-resultMap-（多表查询）"><a href="#2-6-resultMap-（多表查询）" class="headerlink" title="2.6 resultMap （多表查询）"></a>2.6 resultMap （多表查询）</h2><ul>
<li>resultMap属性<ul>
<li>id：resultMap的唯一标识，不可重复</li>
<li>type：java实体类</li>
</ul>
</li>
<li>resultMap字标签<ul>
<li>id：一般对应数据库中该行的主键id，设置此项可提高MyBatis的性能</li>
<li>result：映射到 JavaBean的某个”简单类型“ 属性</li>
<li>association： 映射到 JavaBean的某个“复杂类型”属性，比如 JavaBean类</li>
<li>collection： 映射到 JavaBean的某个”复杂类型“ 属性，比如集合</li>
</ul>
</li>
</ul>
<p>关于 <strong>association</strong> 标签</p>
<ul>
<li><p>用于复杂的类型关联，多对一、一对一（比如一个订单对应一个用户）</p>
</li>
<li><p>内部嵌套： 映射一个嵌套 JavaBean 属性</p>
</li>
<li><p>属性：</p>
<table>
<thead>
<tr>
<th>属性值</th>
<th>描述</th>
</tr>
</thead>
<tbody><tr>
<td>property</td>
<td>映射数据库列的实体对象的属性</td>
</tr>
<tr>
<td>javaType</td>
<td>完整 Java 类名或者别名</td>
</tr>
<tr>
<td>resultMap</td>
<td>引用外部 resultMap</td>
</tr>
</tbody></table>
</li>
<li><p>子标签</p>
<ul>
<li>id</li>
<li>result<ul>
<li>property：映射数据库列的实体对象的属性</li>
<li>column: 数据库列名或者别名</li>
</ul>
</li>
</ul>
</li>
</ul>
<p>关于 <strong>collection</strong> 标签</p>
<ul>
<li>用于复杂类型集合，一对多</li>
<li>内部嵌套：映射一个嵌套结果集到一个列表</li>
<li>属性</li>
</ul>
<table>
<thead>
<tr>
<th>属性值</th>
<th>描述</th>
</tr>
</thead>
<tbody><tr>
<td>property</td>
<td>映射数据库列的实体对象的属性</td>
</tr>
<tr>
<td>ofType</td>
<td>完整 Java类名或者别名（集合所包含的类型）</td>
</tr>
<tr>
<td>resultMap</td>
<td>引用外部resultMap</td>
</tr>
</tbody></table>
<ul>
<li>子标签</li>
<li>id</li>
<li>result<ul>
<li>property：映射数据库列的实体对象的属性</li>
<li>column：数据库列名或者别名</li>
</ul>
</li>
</ul>
<h2 id="2-7-双向关联"><a href="#2-7-双向关联" class="headerlink" title="2.7 双向关联"></a>2.7 双向关联</h2><ul>
<li>如果对象之间互相都有关联关系，称之为双向关联</li>
<li>目的：任何一方都能关联到另一方</li>
<li>上述例子中的 User用户实体类 和 Role 角色实体类便是双向关联</li>
</ul>
<h2 id="2-8-多对多"><a href="#2-8-多对多" class="headerlink" title="2.8 多对多"></a>2.8 多对多</h2><ul>
<li>数据库需要三张表关联，比如订单和商品，中间表则是订单详情表</li>
</ul>
<p>订单 -&gt; 产品</p>
<p>一个订单，多个明细</p>
<p>一个明细，一个产品</p>
<h2 id="2-9-小结"><a href="#2-9-小结" class="headerlink" title="2.9 小结"></a>2.9 小结</h2><p>mapper： namespace</p>
<p>select</p>
<ul>
<li>id</li>
<li>parameterType</li>
<li>resultType &#x2F; resultMap</li>
</ul>
<p>insert、updata、delete</p>
<ul>
<li>id</li>
<li>parameterType</li>
</ul>
<p>resultMap</p>
<ul>
<li>id</li>
<li>type</li>
<li>result</li>
<li>association</li>
<li>collection</li>
</ul>

                
              </div>
            
            <hr/>
            <div>
              <div class="post-metas my-3">
  
    <div class="post-meta mr-3 d-flex align-items-center">
      <i class="iconfont icon-category"></i>
      

<span class="category-chains">
  
  
    
      <span class="category-chain">
        
  <a href="/categories/ORM/" class="category-chain-item">ORM</a>
  
  

      </span>
    
  
</span>

    </div>
  
  
    <div class="post-meta">
      <i class="iconfont icon-tags"></i>
      
        <a href="/tags/MyBatis/">#MyBatis</a>
      
    </div>
  
</div>


              
  

  <div class="license-box my-3">
    <div class="license-title">
      <div>MyBatis 笔记2 SQL映射XML文件标签 select、insert、update _ XML获取多个参数 _ MyBatis实现多表查询 _ resultMap标签映射</div>
      <div>http://example.com/2022/01/14/MyBatis 笔记2 SQL映射XML文件标签 select、insert、update _ XML获取多个参数 _ MyBatis实现多表查询 _ resultMap标签映射/</div>
    </div>
    <div class="license-meta">
      
        <div class="license-meta-item">
          <div>作者</div>
          <div>John Doe</div>
        </div>
      
      
        <div class="license-meta-item license-meta-date">
          <div>发布于</div>
          <div>2022年1月14日</div>
        </div>
      
      
      <div class="license-meta-item">
        <div>许可协议</div>
        <div>
          
            
            
              <a target="_blank" href="https://creativecommons.org/licenses/by/4.0/">
              <span class="hint--top hint--rounded" aria-label="BY - 署名">
                <i class="iconfont icon-by"></i>
              </span>
              </a>
            
          
        </div>
      </div>
    </div>
    <div class="license-icon iconfont"></div>
  </div>



              
                <div class="post-prevnext my-3">
                  <article class="post-prev col-6">
                    
                    
                      <a href="/2022/01/15/Linux%20_%20Shell%E7%AC%94%E8%AE%B02%20Shell%20%E6%B5%81%E7%A8%8B%E6%8E%A7%E5%88%B6%20if%E3%80%81case%E3%80%81for%E3%80%81while_%20read%E8%AF%BB%E5%8F%96%E8%BE%93%E5%85%A5%20_%20%E5%87%BD%E6%95%B0%E7%9A%84%E4%BD%BF%E7%94%A8%20_%20cut%E3%80%81sed%E3%80%81awk%E3%80%81sort%E5%91%BD%E4%BB%A4%20+Demo/" title="Linux _ 笔记2 Shell 流程控制 if、case、for、while_ read读取输入 _ 函数的使用 _ cut、sed、awk、sort命令">
                        <i class="iconfont icon-arrowleft"></i>
                        <span class="hidden-mobile">Linux _ 笔记2 Shell 流程控制 if、case、for、while_ read读取输入 _ 函数的使用 _ cut、sed、awk、sort命令</span>
                        <span class="visible-mobile">上一篇</span>
                      </a>
                    
                  </article>
                  <article class="post-next col-6">
                    
                    
                      <a href="/2022/01/13/Linux%20_%20Shell%E7%AC%94%E8%AE%B01%20Shell%E6%A6%82%E8%BF%B0%20_%20Linux%E7%B3%BB%E7%BB%9F%E8%87%AA%E5%B8%A6%E7%9A%84%E8%A7%A3%E6%9E%90%E5%99%A8_bin_bash%20_%20bash_sh%E6%89%A7%E8%A1%8C%E8%84%9A%E6%9C%AC%20_%20%E8%84%9A%E6%9C%AC%E4%B8%AD$n%20%E3%80%81$#%E7%AD%89%E7%89%B9%E6%AE%8A%E5%8F%98%E9%87%8F%20_%20condition%E6%9D%A1%E4%BB%B6%E5%88%A4%E6%96%AD/" title="Linux _ Shell笔记1 Shell概述 _ Linux系统自带的解析器_bin_bash _ bash_sh执行脚本 _ 脚本中$n 、$#等特殊变量 _ condition条件判断">
                        <span class="hidden-mobile">Linux _ Shell笔记1 Shell概述 _ Linux系统自带的解析器_bin_bash _ bash_sh执行脚本 _ 脚本中$n 、$#等特殊变量 _ condition条件判断</span>
                        <span class="visible-mobile">下一篇</span>
                        <i class="iconfont icon-arrowright"></i>
                      </a>
                    
                  </article>
                </div>
              
            </div>

            
          </article>
        </div>
      </div>
    </div>

    <div class="side-col d-none d-lg-block col-lg-2">
      
  <aside class="sidebar" style="margin-left: -1rem">
    <div id="toc">
  <p class="toc-header"><i class="iconfont icon-list"></i>&nbsp;目录</p>
  <div class="toc-body" id="toc-body"></div>
</div>



  </aside>


    </div>
  </div>
</div>





  



  



  



  



  







    

    
      <a id="scroll-top-button" aria-label="TOP" href="#" role="button">
        <i class="iconfont icon-arrowup" aria-hidden="true"></i>
      </a>
    

    
      <div class="modal fade" id="modalSearch" tabindex="-1" role="dialog" aria-labelledby="ModalLabel"
     aria-hidden="true">
  <div class="modal-dialog modal-dialog-scrollable modal-lg" role="document">
    <div class="modal-content">
      <div class="modal-header text-center">
        <h4 class="modal-title w-100 font-weight-bold">搜索</h4>
        <button type="button" id="local-search-close" class="close" data-dismiss="modal" aria-label="Close">
          <span aria-hidden="true">&times;</span>
        </button>
      </div>
      <div class="modal-body mx-3">
        <div class="md-form mb-5">
          <input type="text" id="local-search-input" class="form-control validate">
          <label data-error="x" data-success="v" for="local-search-input">关键词</label>
        </div>
        <div class="list-group" id="local-search-result"></div>
      </div>
    </div>
  </div>
</div>

    

    
  </main>

  <footer>
    <div class="footer-inner">
  
    <div class="footer-content">
       <a href="https://hexo.io" target="_blank" rel="nofollow noopener"><span>Hexo</span></a> <i class="iconfont icon-love"></i> <a href="https://github.com/fluid-dev/hexo-theme-fluid" target="_blank" rel="nofollow noopener"><span>Fluid</span></a> 
    </div>
  
  
  
  
</div>

  </footer>

  <!-- Scripts -->
  
  <script  src="https://lib.baomitu.com/nprogress/0.2.0/nprogress.min.js" ></script>
  <link  rel="stylesheet" href="https://lib.baomitu.com/nprogress/0.2.0/nprogress.min.css" />

  <script>
    NProgress.configure({"showSpinner":false,"trickleSpeed":100})
    NProgress.start()
    window.addEventListener('load', function() {
      NProgress.done();
    })
  </script>


<script  src="https://lib.baomitu.com/jquery/3.6.0/jquery.min.js" ></script>
<script  src="https://lib.baomitu.com/twitter-bootstrap/4.6.1/js/bootstrap.min.js" ></script>
<script  src="/js/events.js" ></script>
<script  src="/js/plugins.js" ></script>


  <script  src="https://lib.baomitu.com/typed.js/2.0.12/typed.min.js" ></script>
  <script>
    (function (window, document) {
      var typing = Fluid.plugins.typing;
      var subtitle = document.getElementById('subtitle');
      if (!subtitle || !typing) {
        return;
      }
      var text = subtitle.getAttribute('data-typed-text');
      
        typing(text);
      
    })(window, document);
  </script>




  
    <script  src="/js/img-lazyload.js" ></script>
  




  
<script>
  Fluid.utils.createScript('https://lib.baomitu.com/tocbot/4.18.2/tocbot.min.js', function() {
    var toc = jQuery('#toc');
    if (toc.length === 0 || !window.tocbot) { return; }
    var boardCtn = jQuery('#board-ctn');
    var boardTop = boardCtn.offset().top;

    window.tocbot.init({
      tocSelector     : '#toc-body',
      contentSelector : '.markdown-body',
      headingSelector : CONFIG.toc.headingSelector || 'h1,h2,h3,h4,h5,h6',
      linkClass       : 'tocbot-link',
      activeLinkClass : 'tocbot-active-link',
      listClass       : 'tocbot-list',
      isCollapsedClass: 'tocbot-is-collapsed',
      collapsibleClass: 'tocbot-is-collapsible',
      collapseDepth   : CONFIG.toc.collapseDepth || 0,
      scrollSmooth    : true,
      headingsOffset  : -boardTop
    });
    if (toc.find('.toc-list-item').length > 0) {
      toc.css('visibility', 'visible');
    }
  });
</script>


  <script src=https://lib.baomitu.com/clipboard.js/2.0.10/clipboard.min.js></script>

  <script>Fluid.plugins.codeWidget();</script>


  
<script>
  Fluid.utils.createScript('https://lib.baomitu.com/anchor-js/4.3.1/anchor.min.js', function() {
    window.anchors.options = {
      placement: CONFIG.anchorjs.placement,
      visible  : CONFIG.anchorjs.visible
    };
    if (CONFIG.anchorjs.icon) {
      window.anchors.options.icon = CONFIG.anchorjs.icon;
    }
    var el = (CONFIG.anchorjs.element || 'h1,h2,h3,h4,h5,h6').split(',');
    var res = [];
    for (var item of el) {
      res.push('.markdown-body > ' + item.trim());
    }
    if (CONFIG.anchorjs.placement === 'left') {
      window.anchors.options.class = 'anchorjs-link-left';
    }
    window.anchors.add(res.join(', '));
  });
</script>


  
<script>
  Fluid.utils.createScript('https://lib.baomitu.com/fancybox/3.5.7/jquery.fancybox.min.js', function() {
    Fluid.plugins.fancyBox();
  });
</script>


  <script>Fluid.plugins.imageCaption();</script>

  <script  src="/js/local-search.js" ></script>





<!-- 主题的启动项，将它保持在最底部 -->
<!-- the boot of the theme, keep it at the bottom -->
<script  src="/js/boot.js" ></script>


  

  <noscript>
    <div class="noscript-warning">博客在允许 JavaScript 运行的环境下浏览效果更佳</div>
  </noscript>
</body>
</html>
